package com.dzzw.Dao;

import com.dzzw.Model.UMailBox;
import com.dzzw.utils.DButils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

//群众信箱
public class UMailBoxDao {
    private final Connection con = DButils.getCon();
    //信件新增
    public void UMailBoxAdd(UMailBox uMailBox){
        Date user_sendtime = new Date();
        SimpleDateFormat SimpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String sql = "insert into t_user_mailbox(user_id,user_title,user_content,user_sendtime) value (?,?,?,?)";
        try{
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1, uMailBox.getUser_id());
            preparedStatement.setString(2, uMailBox.getUser_title());
            preparedStatement.setString(3, uMailBox.getUser_content());
            preparedStatement.setDate(4, java.sql.Date.valueOf(SimpleDateFormat.format(user_sendtime.getTime())));
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        } catch(Exception e) {
            System.out.println("数据添加失败！");

            e.printStackTrace();
        }
    }

    //信件修改
    public void UMailBoxModify(UMailBox uMailBox){
        String sql = "update t_user_mailbox set  user_title=?,user_content=? where umail_id=?";
        try{
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1, uMailBox.getUser_title());
            preparedStatement.setString(2, uMailBox.getUser_content());
            preparedStatement.setInt(3, uMailBox.getUmail_id());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        } catch(Exception e) {
            System.out.println("数据修改失败！");

            e.printStackTrace();
        }
    }

    //信件删除
    public void UMailBoxDelete(int umail_id) {
        String sql = "Delete from t_user_mailbox where umail_id = ?";
        try {
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1,umail_id);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        }catch(Exception e){
            System.out.println("数据单条删除失败！");

            e.printStackTrace();
        }
    }

    //查询全部信件
    public static List<UMailBox> SelectAllUMailBox() {
        List<UMailBox> list = new ArrayList<>();
        String sql = "SELECT um.umail_id,u.user_name,um.user_title,um.user_content,um.user_sendtime FROM t_user_mailbox AS um INNER JOIN t_user AS u ON um.user_id = u.user_id";
        try{
            Connection con = DButils.getCon();
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                UMailBox uMailBox = new UMailBox();
                uMailBox.setUmail_id(resultSet.getInt(1));
                uMailBox.setUser_name(resultSet.getString(2));
                uMailBox.setUser_title(resultSet.getString(3));
                uMailBox.setUser_content(resultSet.getString(4));
                uMailBox.setUser_sendtime(resultSet.getDate(5));
                list.add(uMailBox);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch(Exception e){
            System.out.println("查询所有数据失败！");
            e.printStackTrace();
        }
        return list;
    }

    //根据用户ID查询信件
    public static List<UMailBox> SelectUMailBoxByUserId(int user_id) {

        List<UMailBox> list = new ArrayList<>();
        String sql = "SELECT um.umail_id,u.user_name,um.user_title,um.user_content,um.user_sendtime FROM t_user_mailbox AS um INNER JOIN t_user AS u ON um.user_id = u.user_id where um.user_id = ?";
        try{
            Connection con = DButils.getCon();
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1,user_id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                UMailBox uMailBox = new UMailBox();
                uMailBox.setUmail_id(resultSet.getInt(1));
                uMailBox.setUser_name(resultSet.getString(2));
                uMailBox.setUser_title(resultSet.getString(3));
                uMailBox.setUser_content(resultSet.getString(4));
                uMailBox.setUser_sendtime(resultSet.getDate(5));
                list.add(uMailBox);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch(Exception e){
            System.out.println("查询所有数据失败！");
            e.printStackTrace();
        }
        return list;
    }
}
